﻿    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Dapper;
    using Domain.Entity;
    using Domain.Entity.bs;
    using Domain.Entity.sys;
    using Domain.Entity.zct;
    using Domain.IRepository;
    using Microsoft.IdentityModel.Tokens;

    namespace EFCore.Repository
    {
        public class WarehouseRepository : IWarehouseRepository
        {
            //连接字符串
            string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

            //添加仓库信息
            // 添加仓库 - 修复返回新仓库ID
            public async Task<int> AddWarehouse(Warehouse warehouse)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();
                    string sql = @"INSERT INTO [dbo].[bs_warehouse] 
                 (wh_code, is_default, wh_name, wh_capacity, wh_depId, wh_depName, 
                  wh_userId, wh_userName, wh_userPhone, wh_address, wh_sort, wh_slot_num,
                  wh_status, is_lock, remark, create_name, create_time)
                 VALUES 
                 (@wh_code, @is_default, @wh_name, @wh_capacity, @wh_depId, @wh_depName, 
                  @wh_userId, @wh_userName, @wh_userPhone, @wh_address, @wh_sort, @wh_slot_num,
                  @wh_status, @is_lock, @remark, @create_name, @create_time);
                 SELECT CAST(SCOPE_IDENTITY() AS INT);";

                    if (warehouse.create_time == null)
                    {
                        warehouse.create_time = DateTime.Now;
                    }
                    if (string.IsNullOrEmpty(warehouse.create_name))
                    {
                        warehouse.create_name = "系统自动生成";
                    }

                    return await sqlConnection.ExecuteScalarAsync<int>(sql, new
                    {
                        warehouse.wh_code,
                        warehouse.is_default,
                        warehouse.wh_name,
                        warehouse.wh_capacity,
                        warehouse.wh_depId,
                        warehouse.wh_depName,
                        warehouse.wh_userId,
                        warehouse.wh_userName,
                        warehouse.wh_userPhone,
                        warehouse.wh_address,
                        warehouse.wh_slot_num,
                        warehouse.wh_sort,
                        warehouse.wh_status,
                        warehouse.is_lock,
                        warehouse.remark,
                        warehouse.create_name,
                        warehouse.create_time
                    });
                }
            }




            //删除仓库信息
            public async Task<int> DeleteWarehouse(int id)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();
                    string sql = $@"update [dbo].[bs_warehouse] set is_delete=1 where wh_id={id}";
                    return await sqlConnection.ExecuteAsync(sql);
                }
            }



            //自动获取联系电话
            public async Task<string> GetPhoneByUserName(string userName)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();
                    string sql = "SELECT user_phone FROM [dbo].[zct_user] WHERE user_name = @userName";
                    return await sqlConnection.QueryFirstOrDefaultAsync<string>(sql, new { userName });
                }
            }

            //分页查询仓库表
            public async Task<FenYe<Warehouse>> GetWarehouse(FenYe<Warehouse> fenYe, int? status, string? whname, string? whdepname, string? whusername)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();

                    // 分页存储过程
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@pageSize", 10);
                    param.Add("@keyName", "wh_id");
                    param.Add("@tabelName", "bs_warehouse");
                    param.Add("@currentPage", fenYe.CurrentPage);
                    param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    //判断条件
                    string sqlparam = $" is_delete=0 ";
                    if (status != null && status != 0)
                        sqlparam += $" and wh_status ={status}";
                    if (!string.IsNullOrEmpty(whname))
                        sqlparam += $" and wh_name like '%{whname}%'";
                    if (!string.IsNullOrEmpty(whdepname))
                        sqlparam += $" and wh_depName like '%{whdepname}%'";
                    if (!string.IsNullOrEmpty(whusername))
                        sqlparam += $" and  wh_userName like '%{whusername}%'";

                    param.Add("@where", sqlparam);

                    string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                    List<Warehouse> list = sqlConnection.Query<Warehouse>(sql, param).ToList();
                    fenYe.List = list;
                    fenYe.Rows = param.Get<int>("@rows");
                    return fenYe;
                }
            }
    

            //查询仓库主管信息
            public async Task<List<User>> GetAllUsers()
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();
                    string sql = "SELECT user_id, user_name, user_phone FROM [dbo].[zct_user] WHERE is_delete = 0";
                    return sqlConnection.Query<User>(sql).ToList();
                }
            }


            //查询仓库名
            public async Task<List<Warehouse>> GetWarehouses()
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();
                    string sql = "SELECT * FROM [dbo].[bs_warehouse]  WHERE is_delete = 0";
                    return sqlConnection.Query<Warehouse>(sql).ToList();
                }
            }

            //根据id查询仓库信息
            public async Task<Warehouse> GetIdWarehouse(int id)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();

                    // 构建查询语句，直接包含必要的条件
                    string sql = "SELECT * FROM  [dbo].[bs_warehouse] WHERE   [wh_id]= @id";

                    // 创建动态参数
                    var parameters = new DynamicParameters();
                    parameters.Add("@id", id);

                    // 执行查询并返回结果集合
                    return await sqlConnection.QueryFirstOrDefaultAsync<Warehouse>(sql, parameters);
                }
            }
            // 修改仓库信息
            public async Task<int> UpdateWarehouse(Warehouse dto)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();

                    // 验证仓库是否存在
                    var exists = await sqlConnection.ExecuteScalarAsync<int>(
                        "SELECT COUNT(1) FROM bs_warehouse WHERE wh_id = @wh_id AND is_delete = 0",
                        new { dto.wh_id });

                    if (exists == 0)
                    {
                        throw new KeyNotFoundException("指定的仓库不存在或已被删除");
                    }

                    // 获取当前库位数量
                    var slotCount = await sqlConnection.ExecuteScalarAsync<int>(
                        "SELECT COUNT(*) FROM bs_slot WHERE wh_id = @wh_id AND is_delete = 0",
                        new { dto.wh_id });

                    string sql = @"UPDATE [dbo].[bs_warehouse] 
                SET 
                    wh_code = @wh_code,
                    is_default = @is_default,
                    wh_name = @wh_name,
                    wh_capacity = @wh_capacity,
                    wh_depId = @wh_depId,
                    wh_depName = @wh_depName,
                    wh_userId = @wh_userId,
                    wh_userName = @wh_userName,
                    wh_userPhone = @wh_userPhone,
                    wh_address = @wh_address,
                    wh_sort = @wh_sort,
                    wh_status = @wh_status,
                    is_lock = @is_lock,
                    remark = @remark,
                    update_name = @update_name,
                    update_time = @update_time,
                    wh_slot_num = @wh_slot_num
                WHERE wh_id = @wh_id";

                    // 创建参数对象，确保所有字段正确映射
                    var parameters = new
                    {
                        dto.wh_id,
                        dto.wh_code,
                        dto.is_default,
                        dto.wh_name,
                        dto.wh_capacity,
                        dto.wh_depId,
                        dto.wh_depName,
                        dto.wh_userId,
                        dto.wh_userName,
                        dto.wh_userPhone,
                        dto.wh_address,
                        dto.wh_sort,
                        dto.wh_status,
                        dto.is_lock,
                        dto.remark,
                        update_name = string.IsNullOrEmpty(dto.update_name) ? "系统自动修改" : dto.update_name,
                        update_time = dto.update_time ?? DateTime.Now,
                        wh_slot_num = slotCount
                    };

                    return await sqlConnection.ExecuteAsync(sql, parameters);
                }
            }
            //分页高级查询

            public async Task<FenYe<Warehouse>> GetvancedWarehouse(FenYe<Warehouse> fenYe, string? code, string? userName, string? name, string? minCapacity, string? maxCapacity, int? status, string? minNum, string? maxNum, string? depName, string? time)
            {
                using (SqlConnection sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();


                    // 分页存储过程
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@pageSize", 10);
                    param.Add("@keyName", "wh_id");
                    param.Add("@tabelName", "bs_warehouse");
                    param.Add("@currentPage", fenYe.CurrentPage);
                    param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                    //判断条件
                    string sqlparam = $" is_delete=0 ";
                    if (!string.IsNullOrEmpty(code))
                        sqlparam += $" and wh_code like '%{code}%'";
                    if (!string.IsNullOrEmpty(name))
                        sqlparam += $" and wh_name like '%{name}%'";
                    if (!string.IsNullOrEmpty(userName))
                        sqlparam += $" and wh_userName like '%{userName}%'";
                    if (!string.IsNullOrEmpty(minCapacity) || !string.IsNullOrEmpty(maxCapacity))
                        sqlparam += $" and  wh_capacity between '{minCapacity}' and '{maxCapacity}'";
                    if (!string.IsNullOrEmpty(minNum) || !string.IsNullOrEmpty(maxNum))
                        sqlparam += $" and  wh_slot_num between '{minNum}' and '{maxNum}'";
                    if (status != null && status != 0)
                        sqlparam += $" and  wh_status={status}";
                    if (!string.IsNullOrEmpty(depName))
                        sqlparam += $" and wh_depName like '%{depName}%'";
                    if (!string.IsNullOrEmpty(time))
                        sqlparam += $" and create_time like '%{time}%'";

                    param.Add("@where", sqlparam);

                    string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                    List<Warehouse> list = sqlConnection.Query<Warehouse>(sql, param).ToList();
                    fenYe.List = list;
                    fenYe.Rows = param.Get<int>("@rows");
                    return fenYe;
                }
            }
        //修改仓库状态
        public async Task<int> UpdateStatus(int id, int wh_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"UPDATE [dbo].[bs_warehouse] SET [wh_status]=@wh_status WHERE [wh_id]=@wh_id";

                var parameters = new DynamicParameters();
                parameters.Add("@wh_status", wh_status); 
                parameters.Add("@wh_id", id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }


        //锁定/解锁仓库
        public async Task<int> LockWarehouse(int whid, int islock)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[bs_warehouse] set is_lock={islock} where wh_id={whid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
